<?php
    session_start();
    include('class.mysql.php');
    $mydb = new mysql();
    $connection = $mydb->Connect();
    
    //Excel File generation
    /** Error reporting */
    error_reporting(E_ALL);

    /** PHPExcel */
    require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
    
    /** PHPExcel_IOFactory */
    require_once 'Classes/PHPExcel/IOFactory.php';


    // Create new PHPExcel object
    //echo date('H:i:s') . " Create new PHPExcel object\n";
    $objPHPExcel = new PHPExcel();

    // Set properties
    //echo date('H:i:s') . " Set properties\n";
    $objPHPExcel->getProperties()->setCreator("Imam Sumarsono")
							 ->setLastModifiedBy("Imam Sumarsono")
							 ->setTitle("")
							 ->setSubject("")
							 ->setDescription("")
							 ->setKeywords("")
							 ->setCategory("");


    // Create a first sheet, representing sales data
    //echo date('H:i:s') . " Add some data\n";
    $objPHPExcel->setActiveSheetIndex(0);
    //$objPHPExcel->getActiveSheet()->setCellValue('A3', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));
    //$objPHPExcel->getActiveSheet()->getStyle('A3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);

    $objPHPExcel->getActiveSheet()->setCellValue('A2', '      Perincian Data Kelompok Kerja');
    $objPHPExcel->getActiveSheet()->setCellValue('A3', 'No');
    $objPHPExcel->getActiveSheet()->setCellValue('B3', 'Nama Kelompok Kerja');
    $objPHPExcel->getActiveSheet()->setCellValue('C3', 'No Registrasi');
    $objPHPExcel->getActiveSheet()->setCellValue('D3', 'Propinsi');
    $objPHPExcel->getActiveSheet()->setCellValue('E3', 'Kab / Kota');
    $objPHPExcel->getActiveSheet()->setCellValue('F3', 'Jenis KK');
    $objPHPExcel->getActiveSheet()->setCellValue('G3', 'Kecamatan');
    $objPHPExcel->getActiveSheet()->setCellValue('H3', 'Anggota');
    $objPHPExcel->getActiveSheet()->setCellValue('I3', 'Nomor HP');
    $objPHPExcel->getActiveSheet()->setCellValue('J3', 'Alamat Email');
    $objPHPExcel->getActiveSheet()->setCellValue('K3', 'Nama Bank');
    $objPHPExcel->getActiveSheet()->setCellValue('L3', 'No Rekening');
    $objPHPExcel->getActiveSheet()->setCellValue('M3', 'Atas Nama');
  
  
    $sreq=$_GET["req"];
    if ($sreq=="all") //jika request nya adalah semua data
    {
        $sql="SELECT no_reg FROM login";
        //echo "$sql";	
        $query = $mydb->Query($sql);
        $num = $mydb->FetchNum($query);
        while($array= mysql_fetch_row($query))
        {
            $no_reg_ar[]=$array[0];
        }
        $rowcount=$num;
    }
    else
        $rowcount=$_GET["rowcount"];
    $i=0;
    for($i=0;$i<$rowcount;$i++)
    {
    	$sdata="no_reg".strval($i+1);
    	if($sreq=="all") //jika request nya adalah semua data
    	    $no_reg=$no_reg_ar[$i];
    	else
    	    $no_reg=$_GET[$sdata];
        $sql="SELECT nama_kk,no_reg,kecamatan,nama_bank,rek_bank,pemilik_rek,email,hp FROM identitas WHERE no_reg='$no_reg'";
        //echo "$sql";	
        $query = $mydb->Query($sql);
        $num = $mydb->FetchNum($query);
        if($array= mysql_fetch_row($query))
        {
	    $nama_kk2=$array[0];
	    if($nama_kk2=="")
	        $nama_kk2='-';
	    $no_reg2=$array[1];
	    if($no_reg2=="")
	        $no_reg2='-';
	    $kecamatan2=$array[2];
	    if($kecamatan2=="")
	        $kecamatan2='-';
	    $nama_bank = $array[3];
	    if($nama_bank=="")
	        $nama_bank='-';
	    $no_rek_bank = $array[4];
	    if($no_rek_bank=="")
	        $no_rek_bank='-';
	    $rek_atas_nama = $array[5];
	    if($rek_atas_nama=="")
	        $rek_atas_nama='-';
	    $email = $array[6];
	    if($email=="")
	        $email='-';
	    $hp = $array[7];
	    if($hp=="")
	        $hp='-';
	        
	    /*Mengambil nama propinsi*/
            $sprop = substr($no_reg2,0,2);
            $skab2 = substr($no_reg2,3,2);
            $sql2="SELECT propinsi FROM propinsi where kode='$sprop'";
            //echo "$sql2";	
            $query2 = $mydb->Query($sql2);
            $num2 = $mydb->FetchNum($query2);
            if($array2= mysql_fetch_row($query2))
            {
	         $nama_prop=$array2[0];
            }
	    //menghitung jumlah anggota kelompok kerja	
	    $sql2="SELECT COUNT(*) FROM anggota WHERE no_reg= '$no_reg2'";
	    //echo "$sql";	
	    $query2 = $mydb->Query($sql2);
	    $num2 = $mydb->FetchNum($query2);
	    //$kabupaten[]="Pilih kota2";
	    if($array2= mysql_fetch_row($query2))
	    {
		$jumlah_anggota = $array2[0];
	    }
            
            //end
	    /*Mengambil nama kabupaten*/
            $sprop = substr($no_reg2,0,2);
            $skab2 = substr($no_reg2,3,2);
            $sql2="SELECT kabupaten FROM kabupaten where kode_prop = '$sprop' and kode='$skab2'";
            //echo "$sql2";	
            $query2 = $mydb->Query($sql2);
            $num2 = $mydb->FetchNum($query2);
            if($array2= mysql_fetch_row($query2))
            {
	         $nama_kab=$array2[0];
            }
            //end
	    //memperbaiki nama kabupaten
	    
	    for($i2=0;$i2<strlen($nama_kab);$i2++)
	    {
                //echo "-".$nama_kab[$i2]."-";
	    	if(chr($nama_kab[$i2])==9)
	    	    $nama_kab[$i2]=0;
	    }
            //mengambil jenis kk
	    if (substr($no_reg2,6,1)=='1')
		$jenis_kk='KKG';
	    else if (substr($no_reg2,6,1)=='2')
	        $jenis_kk ='MGMP';
	    else if (substr($no_reg2,6,1)=='3')
	        $jenis_kk ='KKKS';
	    else if (substr($no_reg2,6,1)=='4')
	        $jenis_kk ='MKKS';
	    else if (substr($no_reg2,6,1)=='5')
	        $jenis_kk ='KKPS';
	    else if (substr($no_reg2,6,1)=='6')
	        $jenis_kk ='MKPS';
	    //mengambil nama kecamatan
            $kecawal=strtoupper(substr($kecamatan2,0,1));
            $kecsisa=strtolower(substr($kecamatan2,1,20));
            $kecamatan2=$kecawal.$kecsisa;	
            $snum=$i+4;
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$snum, $i+1);
	    $objPHPExcel->getActiveSheet()->setCellValue('B'.$snum, $nama_kk2);
	    $objPHPExcel->getActiveSheet()->setCellValue('C'.$snum, $no_reg2);
	    $objPHPExcel->getActiveSheet()->setCellValue('D'.$snum, $nama_prop);
	    $objPHPExcel->getActiveSheet()->setCellValue('E'.$snum, $nama_kab);
	    $objPHPExcel->getActiveSheet()->setCellValue('F'.$snum, $jenis_kk);
	    $objPHPExcel->getActiveSheet()->setCellValue('G'.$snum, $kecamatan2);
	    $objPHPExcel->getActiveSheet()->setCellValue('H'.$snum, $jumlah_anggota);
	    $objPHPExcel->getActiveSheet()->setCellValue('I'.$snum, '['.$hp.']');
	    $objPHPExcel->getActiveSheet()->setCellValue('J'.$snum, $email);
	    $objPHPExcel->getActiveSheet()->setCellValue('K'.$snum, $nama_bank);
	    $objPHPExcel->getActiveSheet()->setCellValue('L'.$snum, '['.$no_rek_bank.']');
	    $objPHPExcel->getActiveSheet()->setCellValue('M'.$snum, $rek_atas_nama);
	    
	    //$sdata2=$sdata2.($i+1).','.'"'.$nama_kk2.'","'.$no_reg2.'","'.$nama_prop.'","'. $nama_kab.'","'.$jenis_kk.'","'.$kecamatan2.'","'.$jumlah_anggota.'","['.$hp.']","'.$email.'","'.$nama_bank.'","['.$no_rek_bank.']","'.$rek_atas_nama.'"';
	    //$sdata2=$sdata2."\n";
        }
        //echo $email;
    	//echo $no_reg[$i];
    	//echo $sdata;
    }
  
    // Set column widths
    //echo date('H:i:s') . " Set column widths\n";
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(9);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(16);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(27);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(34);
    
    //set Row height
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(1);    
    $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(31);    
    
    //Set Font
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getFont()->setName('Candara');
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getFont()->setSize(12);  
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
    $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('Candara');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(20);  
    $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  
    //set fill
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel->getActiveSheet()->getStyle('A3:M3')->getFill()->getStartColor()->setARGB('FF808080');

    //set border
    $styleThinBlackBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN,
			'color' => array('argb' => 'FF000000'),
		),
	),
    );
    for($i3=3;$i3<$rowcount+4;$i3++)
    {
        $objPHPExcel->getActiveSheet()->getStyle('A'.$i3.':M'.$i3)->applyFromArray($styleThinBlackBorderOutline);    
    }
    $itemp=$rowcount+3;
    $objPHPExcel->getActiveSheet()->getStyle('A3:A'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    //$objPHPExcel->getActiveSheet()->getStyle('B3:B'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    $objPHPExcel->getActiveSheet()->getStyle('C3:C'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    //$objPHPExcel->getActiveSheet()->getStyle('D3:D'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    $objPHPExcel->getActiveSheet()->getStyle('E3:E'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    //$objPHPExcel->getActiveSheet()->getStyle('F3:F'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    $objPHPExcel->getActiveSheet()->getStyle('G3:G'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    //$objPHPExcel->getActiveSheet()->getStyle('H3:H'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    $objPHPExcel->getActiveSheet()->getStyle('I3:I'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    //$objPHPExcel->getActiveSheet()->getStyle('J3:J'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    $objPHPExcel->getActiveSheet()->getStyle('K3:K'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    //$objPHPExcel->getActiveSheet()->getStyle('L3:L'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    $objPHPExcel->getActiveSheet()->getStyle('M3:M'.$itemp)->applyFromArray($styleThinBlackBorderOutline);
    
    // Add a drawing to the worksheet
    //echo date('H:i:s') . " Add a drawing to the worksheet\n";
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    $objDrawing->setName('Logo');
    $objDrawing->setDescription('Logo');
    $objDrawing->setPath('images/diknas.png');
    $objDrawing->setCoordinates('A2');
    //$objDrawing->setHeight(40);
    $objDrawing->setWidth(40);
    $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
    
    // Rename sheet
    //echo date('H:i:s') . " Rename sheet\n";
    $objPHPExcel->getActiveSheet()->setTitle('Perincian Data Kelompok kerja');
    
	//Hide some column regarding user setting
	if($_COOKIE["ck_cc2"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
	if($_COOKIE["ck_cc3"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
	if($_COOKIE["ck_cc4"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setVisible(false);
	if($_COOKIE["ck_cc5"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setVisible(false);
	if($_COOKIE["ck_cc6"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setVisible(false);
	if($_COOKIE["ck_cc7"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setVisible(false);
	if($_COOKIE["ck_cc8"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setVisible(false);
	if($_COOKIE["ck_cc9"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setVisible(false);
	if($_COOKIE["ck_cc10"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setVisible(false);
	if($_COOKIE["ck_cc11"]=="off")
		$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setVisible(false);
			
    // Redirect output to a clients web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="TabelExcel.xls"');
    header('Cache-Control: max-age=0');
    
    //do generate excel
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    //$objWriter->save(str_replace('.php', '.xls', "TabelExcel.xls"));
    $objWriter->save('php://output');
    //header("Location: TabelExcel.xls");
    exit;
?>